package bd;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

// Clase Singleton 

public class Servicios {
	private static Servicios instancia;
	private java.sql.Connection conexionBD;
	private String usuario;
	private String empleado;
	private int legajo = 0;
	private int tarjeta = 0;
	private int pin = 0;
	private float saldo;

	private Servicios() {
	}

	public static Servicios getServicios() {
		if (instancia == null) {
			instancia = new Servicios();
			return instancia;
		} else
			return instancia;
	}

	public String conectarBD(String usuario, String clave) {
		if (this.conexionBD == null) {
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
			} catch (Exception ex) {
				System.out.println(ex.getMessage());
			}

			try {
				String servidor = "localhost:3306";
				String baseDatos = "banco";
				String uriConexion = "jdbc:mysql://" + servidor + "/"
						+ baseDatos;
				this.conexionBD = DriverManager.getConnection(uriConexion,
						usuario, clave);
				this.usuario = usuario;
			} catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return "Se produjo un error al intentar conectarse a la base de datos.\n"
						+ ex.getMessage() + "Error";
			}
		}
		return null;
	}

	public void desconectarBD() {
		if (this.conexionBD != null) {
			try {
				this.conexionBD.close();
				this.conexionBD = null;
			} catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
		}
	}

	public boolean verifTarjeta(int tarjeta, int pin) {
		try {
			this.tarjeta = tarjeta;
			this.pin = pin;

			java.sql.Statement stmt = conexionBD.createStatement();
			java.sql.ResultSet rs = stmt
					.executeQuery("SELECT pin,nro_tarjeta, saldo from Tarjeta, trans_cajas_ahorro where nro_ca = caja_ahorro group by nro_tarjeta");
			while (rs.next()) {
				if (rs.getInt("nro_tarjeta") == tarjeta
						&& rs.getInt("pin") == pin) {
					this.saldo = rs.getFloat("saldo");
					rs.close();
					stmt.close();
					return true;
				}

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean conectarEmpleado(int legajo, String passw) {
		this.legajo = legajo;
		try {
			java.sql.Statement stmt = conexionBD.createStatement();
			java.sql.ResultSet rs = stmt
					.executeQuery("SELECT legajo, password, apellido, nombre FROM Empleado");
			while (rs.next()) {
				if (rs.getInt("legajo") == legajo
						&& rs.getString("password").equals(passw)) {
					this.empleado = rs.getString("apellido") + ", "
							+ rs.getString("nombre");
					rs.close();
					stmt.close();
					return true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public Vector<String> getCajeros() {

		Vector<String> cajeros = new Vector<>();
		try {
			java.sql.Statement stmt;
			stmt = conexionBD.createStatement();

			java.sql.ResultSet rs = stmt.executeQuery("SELECT * FROM caja;");

			while (rs.next()) {
				cajeros.add(rs.getString(1));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cajeros;

	}

	public Resultado realizarConsulta(String consulta) {
		try {
			Statement stmt = conexionBD.createStatement();
			boolean aux = stmt.execute(consulta);
			ResultSet rs;
			if (aux) {
				rs = stmt.getResultSet();
				ResultSetMetaData rsmd = rs.getMetaData();

				int max = rsmd.getColumnCount();

				String[] columnas = new String[max];
				String[] tipos = new String[max];
				Vector<Object[]> filas = new Vector<Object[]>();

				for (int i = 1; i <= max; i++) {
					columnas[i - 1] = rsmd.getColumnLabel(i);
					tipos[i - 1] = rsmd.getColumnClassName(i);
				}

				while (rs.next()) {
					Vector<Object> vector = new Vector<Object>();
					for (int c = 1; c <= max; c++)
						vector.add(rs.getObject(c));
					filas.add(vector.toArray());
				}

				rs.close();
				stmt.close();

				return new Resultado(columnas, filas, tipos);
			} else
				return null;
		} catch (Exception e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + ((SQLException) e).getSQLState());
			System.out.println("VendorError: "
					+ ((SQLException) e).getErrorCode());
			return new Resultado(e);
		}

	}

	public String[] getMeses() {
		try {
			java.sql.Statement stmt = conexionBD.createStatement();
			java.sql.ResultSet rs = stmt
					.executeQuery("SELECT periodo FROM Tasa_Prestamo GROUP BY periodo");
			String[] cantMeses;
			int i = 0;
			while (rs.next()) {
				i++;
			}
			cantMeses = new String[i];
			rs.first();
			i = 0;
			cantMeses[i] = rs.getString("periodo");
			while (rs.next()) {
				i++;
				cantMeses[i] = rs.getString("periodo");
			}
			rs.close();
			stmt.close();
			return cantMeses;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public String[] getTipoDoc() {
		try {
			java.sql.Statement stmt = conexionBD.createStatement();
			java.sql.ResultSet rs = stmt
					.executeQuery("SELECT tipo_doc FROM Cliente GROUP BY tipo_doc");
			String[] cantTipo;
			int i = 0;
			while (rs.next()) {
				i++;
			}
			cantTipo = new String[i];
			rs.first();
			i = 0;
			cantTipo[i] = rs.getString("tipo_doc");
			while (rs.next()) {
				i++;
				cantTipo[i] = rs.getString("tipo_doc");
			}
			rs.close();
			stmt.close();
			return cantTipo;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public float getMaximoMontoPrestamo() {
		try {
			java.sql.Statement stmt = conexionBD.createStatement();
			java.sql.ResultSet rs = stmt
					.executeQuery("SELECT max(monto_sup) FROM Tasa_Prestamo");
			float r;
			if (rs.first()) {
				r = Float.parseFloat(rs.getString("max(monto_sup)"));
				rs.close();
				stmt.close();
				return r;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	public float getInteresPrestamo(float m, int cantM) {
		try {
			java.sql.Statement stmt = conexionBD.createStatement();
			java.sql.ResultSet rs = stmt
					.executeQuery("SELECT monto_inf, monto_sup, tasa FROM Tasa_Prestamo WHERE periodo ="
							+ cantM);
			float max, r;
			while (rs.next()) {
				max = Float.parseFloat(rs.getString("monto_sup"));
				if (m <= max) {
					r = Float.parseFloat(rs.getString("tasa"));
					rs.close();
					stmt.close();
					return r;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	@SuppressWarnings("deprecation")
	public boolean crearPrestamo(int meses, float monto, float tasa,
			float interes, float cuota, int legajo, int cliente) {
		java.util.Date fecha = new Date();
		java.sql.Date r = java.sql.Date.valueOf((new SimpleDateFormat(
				"yyyy-MM-dd")).format(fecha));

		try {
			java.sql.Statement stmt = conexionBD.createStatement();
			stmt.executeUpdate("INSERT INTO Prestamo (fecha, cant_meses, monto, tasa_interes, interes, valor_cuota, legajo, nro_cliente) VALUES ('"
					+ r
					+ "', '"
					+ meses
					+ "', '"
					+ monto
					+ "', '"
					+ tasa
					+ "', '"
					+ interes
					+ "', '"
					+ cuota
					+ "', '"
					+ legajo
					+ "', '" + cliente + "')");

			/*
			 * ESTE CODIGO INSERTA LOS RESPECTIVOS PAGOS DE UN PRESTAMO, COMO
			 * EXISTE UN TRIGGER EN LA BD NO ES NECESARIO SU IMPLEMENTACION
			 * 
			 * 
			 * java.sql.ResultSet rs = stmt
			 * .executeQuery("SELECT nro_prestamo FROM Prestamo WHERE legajo ="
			 * + legajo + " and nro_cliente like '" + cliente +
			 * "' and fecha = '" + r + "'"); rs.first(); int nro =
			 * Integer.parseInt(rs.getString("nro_prestamo")); int i;
			 * 
			 * for (i = 1; i <= meses; i++) { r.setMonth(r.getMonth() + 1); if
			 * (r.getDate() >= 28 && r.getMonth() == 2) r.setDate(28); else if
			 * (r.getDate() == 31 && (r.getMonth() == 4 || r.getMonth() == 6 ||
			 * r.getMonth() == 9 || r.getMonth() == 11)) r.setDate(30);
			 * 
			 * stmt.executeUpdate(
			 * "INSERT INTO Pago (nro_prestamo, nro_pago, fecha_venc) VALUES ('"
			 * + nro + "', '" + i + "', '" + r + "')"); }
			 * 
			 * rs.close();
			 */
			stmt.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;

	}

	public boolean realizarPago(Long[] pagos, Long prestamo) {
		java.util.Date fecha = new Date();
		java.sql.Date r = java.sql.Date.valueOf((new SimpleDateFormat(
				"yyyy-MM-dd")).format(fecha));

		try {
			int c = pagos.length;
			java.sql.Statement stmt = conexionBD.createStatement();
			int i;
			for (i = 0; i < c; i++) {
				stmt.executeUpdate("UPDATE Pago SET fecha_pago = '" + r
						+ "' WHERE nro_prestamo = " + prestamo
						+ " and nro_pago = '" + pagos[i] + "'");
			}
			stmt.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public String transferir(float f, int caja_destino, int cajero) {
		String Resultado = "";
		try {
			java.sql.Statement stmt = conexionBD.createStatement();

			java.sql.ResultSet rs = stmt.executeQuery("call transferir(" + f
					+ "," + this.tarjeta + "," + caja_destino + "," + cajero
					+ ")");
			if (rs.next())
				Resultado = rs.getString(1);
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			Resultado = e.getMessage();
			e.printStackTrace();
		}
		return Resultado;

	}

	public String extraer(float f, int cajero) {
		String Resultado = "";

		try {
			java.sql.Statement stmt = conexionBD.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery("call extraer(" + f + ","
					+ this.tarjeta + "," + cajero + ")");
			if (rs.next())
				Resultado = rs.getString(1);
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			Resultado = e.getMessage();
			e.printStackTrace();
		}
		return Resultado;

	}

	public String getUsuario() {
		return usuario;
	}

	public int getTarjeta() {
		return tarjeta;
	}

	public int getLegajo() {
		return legajo;
	}

	public int getPin() {
		return pin;
	}

	public float getSaldo() {
		return saldo;
	}

	public String getEmpleado() {
		return empleado;
	}

	public float getSaldo(int tarj) {
		float saldo = -1;
		try {
			java.sql.Statement stmt = conexionBD.createStatement();
			java.sql.ResultSet rs = stmt
					.executeQuery("SELECT saldo from Tarjeta natural join trans_cajas_ahorro where nro_tarjeta="
							+ tarj);
			if (rs.next())
				saldo = rs.getFloat(1);
			stmt.close();
			rs.close();
			
			return saldo;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return saldo;

	}
}
